﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using KTX.Thoai;

namespace KTX.Thoai
{
    public partial class sodien : Form
    {
        private SqlConnection cnn;
        private SqlCommand cmd;
        private DataTable tb;
        private Boolean them;

        public sodien()
        {
            InitializeComponent();
        }

        private void sodien_Load(object sender, EventArgs e)
        {
            Configuration _config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            var strConnectionString = _config.ConnectionStrings.ConnectionStrings[1].ConnectionString;

            cnn = new SqlConnection(strConnectionString);

            cmdsave.Enabled = false;
        }

        private void LoadData()
        {
            if (cnn.State == ConnectionState.Closed) cnn.Open();
            cmd = new SqlCommand("select * from tb_Electricity", cnn);
            tb = new DataTable();
            tb.Load(cmd.ExecuteReader());

            dgv1.DataSource = tb;
        }

        private void EditData()
        {
            int r = dgv1.CurrentCell.RowIndex;

            this.txtid.Text = dgv1.Rows[r].Cells[0].Value.ToString();
            this.cmbroomid.SelectedValue = dgv1.Rows[r].Cells[1].Value.ToString();
            this.txtmonth.Text = dgv1.Rows[r].Cells[2].Value.ToString();
            this.txtoldnumber.Text = dgv1.Rows[r].Cells[3].Value.ToString();
            this.txtnewnumber.Text = dgv1.Rows[r].Cells[4].Value.ToString();                        
        }

        private void cmdload_Click(object sender, EventArgs e)
        {
            try
            {
                LoadData();
                //gbox1.Enabled = false;
                EditData();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void cmdadd_Click(object sender, EventArgs e)
        {
            them = true;
            cmdsave.Enabled = true;
            txtid.Focus();

            //đưa dữ liệu từ tb_Room vào RoomId
            if (cnn.State == ConnectionState.Closed) cnn.Open();

            cmd = new SqlCommand("select * from tb_Room", cnn);

            tb = new DataTable();

            tb.Load(cmd.ExecuteReader());

            this.cmbroomid.DataSource = tb;
            this.cmbroomid.DisplayMember = "RoomId";
            this.cmbroomid.ValueMember = "Id";
        }

        private void cmdsua_Click(object sender, EventArgs e)
        {
            them = false;
            cmdload.Enabled = false;
            cmdexit.Enabled = false;
            cmdsave.Enabled = true;
            cmdexit.Enabled = true;

            this.cmbroomid.DataSource = tb;
            this.cmbroomid.DisplayMember = "RoomId";
            this.cmbroomid.ValueMember = "Id";

            EditData();
            this.txtid.Focus();
        }

        private void cmdexit_Click(object sender, EventArgs e)
        {
            cnn.Close();
            Application.Exit();
        }

        private void cmdsave_Click(object sender, EventArgs e)
        {
            if (them)
            {
                try
                {
                    if (cnn.State == ConnectionState.Closed) cnn.Open();
                    var sql = string.Format("insert into  tb_Electricity(RoomId,Month,OldNumber,NewNumber) values ({0},'{1}',{2},{3})", cmbroomid.SelectedValue, txtmonth.Text, txtoldnumber.Text, txtnewnumber.Text);

                    // Toan sua:
                    // var sql = string.Format("insert into  tb_Electricity(RoomId,Month,OldNumber,NewNumber) values ({0},'{1}',{2},{3})", cmbroomid.SelectedValue, txtmonth.Text, txtoldnumber.Text, txtnewnumber.Text);

                    cmd = new SqlCommand(sql, cnn);
                    cmd.ExecuteNonQuery();
                                        
                    LoadData();

                    MessageBox.Show("Đã thêm thành công", "Thông Báo", MessageBoxButtons.OK);
                    txtid.Clear();
                    txtmonth.Clear();
                    txtoldnumber.Clear();
                    txtnewnumber.Clear();
                    txtid.Focus();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            else
            {
                try
                {
                    if (cnn.State == ConnectionState.Closed) cnn.Open();
                    dgv1.Enabled = true;
                    cmd.CommandType = CommandType.Text;

                    //dòng hiện hành
                    int r = dgv1.CurrentCell.RowIndex;

                    //id hiện hành
                    string strid = dgv1.Rows[r].Cells[0].Value.ToString();

                    //thực hiện lệnh update
                    cmd.CommandText = System.String.Concat("Update tb_Electricity Set RoomId=' " + this.cmbroomid.SelectedValue.ToString() + " ', Month=' " + this.txtmonth.Text.ToString() + " ', OldNumber= ' " + this.txtoldnumber.Text.ToString() + " ', NewNumber= ' " + this.txtnewnumber.Text.ToString() + " ' Where Id= ' " + strid + " ' ");

                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();

                    LoadData();
                    MessageBox.Show("Đã sửa xong", "Thông Báo", MessageBoxButtons.OK);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            cnn.Close();
        }
    }
}